﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 数据访问类:Item
    /// </summary>
    public partial class Item
    {
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsCls(string itemClsNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from dbo.t_Item");
            strSql.Append(" where FItemClsNo=@FItemClsNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FItemClsNo", SqlDbType.NVarChar,6)			};
            parameters[0].Value = itemClsNo;


            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="itemModel">model对象</param>
        /// <returns>是否增加</returns>
        public void Add(ElingOA6_Admin_DSJCJ.Model.Item itemModel, Model.ItemYearBal itemYearBalModel)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            strSql.Append("insert into dbo.t_Item(");
            strSql.Append("FItemNo,FEntityNo,FItemClsNo,FItemName,FItemSpec,FUnit,FSafetyStock,FIsUse)");
            strSql.Append(" values (");
            strSql.Append("@FItemNo,@FEntityNo,@FItemClsNo,@FItemName,@FItemSpec,@FUnit,@FSafetyStock,@FIsUse)");
            SqlParameter[] parameters = {
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FItemClsNo", SqlDbType.NVarChar,6),
					new SqlParameter("@FItemName", SqlDbType.NVarChar,30),
					new SqlParameter("@FItemSpec", SqlDbType.NVarChar,30),
					new SqlParameter("@FUnit", SqlDbType.NVarChar,6),
					new SqlParameter("@FSafetyStock", SqlDbType.Decimal,13),
					new SqlParameter("@FIsUse", SqlDbType.NVarChar,1)};
            parameters[0].Value = itemModel.FItemNo;
            parameters[1].Value = itemModel.FEntityNo;
            parameters[2].Value = itemModel.FItemClsNo;
            parameters[3].Value = itemModel.FItemName;
            parameters[4].Value = itemModel.FItemSpec;
            parameters[5].Value = itemModel.FUnit;
            parameters[6].Value = itemModel.FSafetyStock;
            parameters[7].Value = itemModel.FIsUse;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            ///

            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("insert into dbo.t_ItemYearBal(");
            strSql.Append("FYear,FItemNo,FEntityNo,FPrice,FQty,FAmount)");
            strSql.Append(" values (");
            strSql.Append("@FYear,@FItemNo,@FEntityNo,@FPrice,@FQty,@FAmount)");
            SqlParameter[] parameters1 = {
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FPrice", SqlDbType.Decimal,13),
					new SqlParameter("@FQty", SqlDbType.Decimal,13),
					new SqlParameter("@FAmount", SqlDbType.Decimal,13)};
            parameters1[0].Value = itemYearBalModel.FYear;
            parameters1[1].Value = itemYearBalModel.FItemNo;
            parameters1[2].Value = itemYearBalModel.FEntityNo;
            parameters1[3].Value = itemYearBalModel.FPrice;
            parameters1[4].Value = itemYearBalModel.FQty;
            parameters1[5].Value = itemYearBalModel.FAmount;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters1;
            cmdList.Add(cmds);



            DbHelperSQL.ExecuteSqlTranWithString(cmdList);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">model对象</param>
        /// <returns>是否更新</returns>
        public bool Update(ElingOA6_Admin_DSJCJ.Model.Item model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update dbo.t_Item set ");
            strSql.Append("FItemName=@FItemName,");
            strSql.Append("FItemSpec=@FItemSpec,");
            strSql.Append("FUnit=@FUnit,");
            strSql.Append("FSafetyStock=@FSafetyStock,");
            strSql.Append("FIsUse=@FIsUse,");
            strSql.Append("FItemClsNo=@FItemClsNo");
            strSql.Append(" where FItemNo=@FItemNo and FEntityNo=@FEntityNo");
            SqlParameter[] parameters = {
					new SqlParameter("@FItemName", SqlDbType.NVarChar,30),
					new SqlParameter("@FItemSpec", SqlDbType.NVarChar,30),
					new SqlParameter("@FUnit", SqlDbType.NVarChar,6),
					new SqlParameter("@FSafetyStock", SqlDbType.Decimal,13),
					new SqlParameter("@FIsUse", SqlDbType.NVarChar,1),
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FItemClsNo", SqlDbType.NVarChar,6)};
            parameters[0].Value = model.FItemName;
            parameters[1].Value = model.FItemSpec;
            parameters[2].Value = model.FUnit;
            parameters[3].Value = model.FSafetyStock;
            parameters[4].Value = model.FIsUse;
            parameters[5].Value = model.FItemNo;
            parameters[6].Value = model.FEntityNo;
            parameters[7].Value = model.FItemClsNo;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }



        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="itemNo">用品编号</param>
        /// <param name="entityNo">公司编号</param>
        /// <returns>实体对象</returns>
        public ElingOA6_Admin_DSJCJ.Model.Item GetModel(string itemNo, string entityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FItemNo,FEntityNo,FItemClsNo,FItemClsName,FItemName,FItemSpec,FUnit,FSafetyStock,FIsUse,FPrice,FQty,FAmount from dbo.v_Item_Cls ");
            strSql.Append(" where FItemNo=@FItemNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = itemNo;
            parameters[1].Value = entityNo;

            ElingOA6_Admin_DSJCJ.Model.Item model = new ElingOA6_Admin_DSJCJ.Model.Item();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="row">行</param>
        /// <returns>得到一个对象实体</returns>
        public ElingOA6_Admin_DSJCJ.Model.Item DataRowToModel(DataRow row)
        {
            ElingOA6_Admin_DSJCJ.Model.Item model = new ElingOA6_Admin_DSJCJ.Model.Item();
            if (row != null)
            {
                if (row["FItemNo"] != null)
                {
                    model.FItemNo = row["FItemNo"].ToString();
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["FItemClsNo"] != null)
                {
                    model.FItemClsNo = row["FItemClsNo"].ToString();
                }
                if (row.Table.Columns.Contains("FItemClsName"))
                {
                    if (row["FItemClsName"] != null)
                    {
                        model.FItemClsName = row["FItemClsName"].ToString();
                    }
                }
                if (row["FItemName"] != null)
                {
                    model.FItemName = row["FItemName"].ToString();
                }
                if (row["FItemSpec"] != null)
                {
                    model.FItemSpec = row["FItemSpec"].ToString();
                }
                if (row["FUnit"] != null)
                {
                    model.FUnit = row["FUnit"].ToString();
                }
                if (row["FSafetyStock"] != null && row["FSafetyStock"].ToString() != "")
                {
                    model.FSafetyStock = decimal.Parse(row["FSafetyStock"].ToString());
                }
                if (row["FIsUse"] != null)
                {
                    model.FIsUse = row["FIsUse"].ToString();
                }
                if (row["FPrice"] != null && row["FPrice"].ToString() != "")
                {
                    model.FPrice = decimal.Parse(row["FPrice"].ToString());
                }
                if (row["FQty"] != null && row["FQty"].ToString() != "")
                {
                    model.FQty = decimal.Parse(row["FQty"].ToString());
                }
                if (row["FAmount"] != null && row["FAmount"].ToString() != "")
                {
                    model.FAmount = decimal.Parse(row["FAmount"].ToString());
                }

            }
            return model;
        }
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="itemNo">用品编号</param>
        /// <param name="entityNo">公司编号</param>
        /// <returns>是否存在该记录</returns>
        public bool Exists(string itemNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from dbo.t_Item");
            strSql.Append(" where FItemNo=@FItemNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = itemNo;
            parameters[1].Value = entityNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 批量删除
        /// <creator>chenly</creator>
        /// <date>2013-9-23</date>
        /// </summary>
        /// <param name="itemArray">编号数组</param>
        /// <param name="entityNo">公司编号</param>
        public void DelManyItem(string[] itemArray, string entityNo, string year)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            if (itemArray.Length > 0)
            {
                strSql = new StringBuilder();
                strSql.Append("delete from dbo.t_ItemYearBal ");
                strSql.Append(" where FYear=@FYear and FItemNo=@FItemNo and FEntityNo=@FEntityNo ");
                string itemYearBalSql = strSql.ToString();


                //删除
                strSql = new StringBuilder();
                strSql.Append("delete from dbo.t_Item ");
                strSql.Append(" where FItemNo=@FItemNo and FEntityNo=@FEntityNo ");
                string itemSql = strSql.ToString();

                foreach (string itemNo in itemArray)
                {
                    cmds = new CommandInfo();

                    SqlParameter[] parameters = {
					        new SqlParameter("@FYear", SqlDbType.Decimal,5),
					        new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					        new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
                    parameters[0].Value = year;
                    parameters[1].Value = itemNo;
                    parameters[2].Value = entityNo;

                    cmds.CommandText = itemYearBalSql;
                    cmds.Parameters = parameters;
                    cmdList.Add(cmds);


                    cmds = new CommandInfo();

                    cmds.CommandText = itemSql;
                    cmds.Parameters = parameters;
                    cmdList.Add(cmds);

                }

            }

            DbHelperSQL.ExecuteSqlTranWithString(cmdList);

        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(string itemNo, string entityNo, string year)
        {

            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();

            strSql.Append("delete from dbo.t_ItemYearBal ");
            strSql.Append(" where FYear=@FYear and FItemNo=@FItemNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = year;
            parameters[1].Value = itemNo;
            parameters[2].Value = entityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            ///
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            cmdList = new List<CommandInfo>();

            strSql.Append("delete from dbo.t_Item ");
            strSql.Append(" where FItemNo=@FItemNo and FEntityNo=@FEntityNo");

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

           


            DbHelperSQL.ExecuteSqlTranWithString(cmdList);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListAll(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FItemNo,FEntityNo,FItemClsNo,FItemName,FItemSpec,FUnit,FSafetyStock,FIsUse,FPrice,FQty,FAmount ");
            strSql.Append(" FROM dbo.t_Item ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        /// <param name="strWhere">where条件</param>
        /// <returns>表</returns>
        public DataSet GetList(string message)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FItemNo,FEntityNo,FItemClsNo,FItemClsName,FItemName,FItemSpec,FUnit,FSafetyStock,FIsUse,FPrice,FQty,FAmount ");
            strSql.Append(" FROM dbo.v_Item_Cls");
            strSql.Append(" where  FItemNo in(" + message);
            strSql.Append(" )");
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 得到名称
        /// </summary>
        /// <param name="itemName">类别名称</param>
        /// <returns>名称</returns>
        public string GetItemNoByName(string itemName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FItemNo,FEntityNo,FItemClsNo,FItemName,FItemSpec,FUnit,FSafetyStock,FIsUse,FPrice,FQty,FAmount ");
            strSql.Append(" FROM dbo.t_Item ");
            strSql.Append(" where  FItemName=@FItemName");

            SqlParameter[] parameters = {
					        new SqlParameter("@FItemName", SqlDbType.NVarChar,30)};
            parameters[0].Value = itemName;


            return DbHelperSQL.GetString(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到编号
        /// </summary>
        /// <param name="itemName">类别名称</param>
        /// <returns>编号</returns>
        public string GetItemNo(string itemNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FItemName");
            strSql.Append(" FROM dbo.t_Item ");
            strSql.Append(" where  FItemNo=@FItemNo");

            SqlParameter[] parameters = {
					        new SqlParameter("@FItemNo", SqlDbType.NVarChar,30)};
            parameters[0].Value = itemNo;


            return DbHelperSQL.GetString(strSql.ToString(), parameters);
        }
    }
}
